Azure DocumentDB - Part 6 (DocumentDB 中的 SQL 語法 1)

Microsoft Azure DocumentDB 可以讓開發人員使用熟悉的 SQL 語法來查詢 json 文件，大大降低了開發上的進入門檻

1. 支援 SQL 查詢，而不是發明新的 JSON 查詢語法
2. DocumentDB 身為一個 JSON document databse，可以直接在 database 中執行 Javascript,而 DocumentDB SQL 則是以 JavaScript 的 type system, expression evaluation, 及 function 叫用為基礎.

DocumentDB SQL 特性

1. 如同 JOSN 一樣的樹狀結構，可以讀取一層一層的節點
2. 動態繫結類型系統 - 結構化查詢語言( SQL ) 查詢 無結構化描述資料( JSON )，不保證會是固定結構型式
3. 只支援嚴謹的 JSON 格式，規格可以看 JSON 規格
4. Document 間(每個 JSON 間)的關聯是使用 內嵌資料(一種反正規化模型) 來處理，不是 primary key 跟 foreign key，關於內嵌資料可以看 在 DocumentDB 中模型化資料

DocumentDB SQL 基礎

• 查詢是以唯讀形式執行，可以在主要或次要複本上執行
• 只有 SELECT 是必要的，其他皆可省略
  SELECT [TOP <top_expression>] <select_list>
  [FROM <from_specification>]
  [WHERE <filter_condition>]
  [ORDER BY <sort_specification]

1. SELECT

• 唯一必要的部份
• 將篩選出的 JSON 值用來產生新的 JSON (回傳用)

1. 純量運算式

   SELECT { id: 1, name: 'yowko'}

2. 隱性參數變數

   若未提供索引鍵，會自動建立 隱性參數變數 $1，多個物件就會有多個，數字遞增

3. 別名( alias )

   可以使用別名( alias )來重新命名回傳 JSON

4. 物件和陣列建立

   SELECT [f.address.city, f.address.state] AS CityState FROM Families f

   [
        {
          "CityState": [
            "seattle", 
            "WA"
          ]
        }, 
        {
          "CityState": [
            "NY", 
            "NY"
          ]
        }
   ]
   

5. VALUE 關鍵字

• 只回傳 JSON 值的方法 (不包含層名稱)
• e.g. SELECT VALUE "Hello World"

  ["Hello World"]
  

• 不是

  {$1: "Hello World"}
  

6. * 運算子

• 只能單獨存在，如果有其他屬性就會無效

7. TOP 運算子

• 用來限定查詢的數量

2. FROM

• [FROM <from_specification>] 非必要
• 指定資料來源
• 查詢強制規則
  • 可以使用別名( alias )

    e.g. SELECT f.id FROM Families AS f or SELECT f.id FROM Families f

  • 使用別名( alias )後，無法再繫結原始名稱
  • 屬性參考需要完整描述

    e.g. SELECT id FROM Families f 是無效的

• sub-document
  • 來源可以指定子目錄

    e.g. SELECT * FROM Families.children

  • 來源非有效 JOSN 值，會直接排除 e.g. 部份物件沒有 address.state 值，SELECT * FROM Families.address.state 時就會被當做不在範圍內排除

3. WHERE

• WHERE <filter_condition> 非必要
• 條件不符合 或是 沒有指定的屬性 皆會被排除

1. 支援的一元( unary )運算子(處理一個運算元)：+, - , ~ and NOT

2. 支援的二元( binary )運算子(處理二個運算元)

   Type	運算子

   算術| +,-,*,/,%
   位元|｜,&, ^, <<, >>, >>> (右移位元並補零）
   邏輯| AND, OR, NOT
   比較| =, !=, <, >, <=, >=, <>
   字串| ｜｜ 接(連)

3. 三元 (?) 運算子

• a == b ? 1 : 0
• 可用來建構新的 JSON
• 可巢狀使用

4. 聯合 (??) 運算子

• 用來檢查屬性是否存在，並在不存在時直接定義

5. BETWEEN

• 範圍查詢
• 可用於 字串 及 數字
• 使用範圍索引類型可以優化查詢時間

6. IN

• 檢查指定 JOSN 值是否符合清單中的任一個

7. 邏輯 (AND、OR 和 NOT) 運算子

• AND

  -| True| False| Undefined
  -|-
  True| True| False| Undefined
  False| False| False| False
  Undefined| Undefined| False| Undefined

• OR

  -| True| False| Undefined
  -|-
  True| True| True| True
  False| True| False| Undefined
  Undefined| True| Undefined| Undefined

• NOT

  Origin|Result
  -|
  True| False
  False| True
  Undefined| Undefined

8. 相等和比較運算子

• 不同類型比較會導致 Undefined

• Undefined 就代表會被排除

  -| Undefined| Null| Boolean| Number| String| Object| Array
  -|-
  Undefined| Undefined| Undefined| Undefined |Undefined |Undefined |Undefined| Undefined
  Null| Undefined| OK| Undefined| Undefined| Undefined| Undefined| Undefined
  Boolean| Undefined| Undefined| OK| Undefined| Undefined| Undefined| Undefined
  Number| Undefined| Undefined| Undefined| OK| Undefined| Undefined| Undefined
  String| Undefined| Undefined| Undefined| Undefined| OK| Undefined| Undefined
  Object| Undefined| Undefined| Undefined| Undefined| Undefined| OK| Undefined
  Array| Undefined| Undefined| Undefined| Undefined| Undefined| Undefined| OK

9. 加上引號 [] 的屬性存取子

• 用來解決屬性中有 空格 、特殊字元，SQL 保留字
• e.g.

  SELECT f["lastName"]
  FROM Families f
  WHERE f["id"] = "AndersenFamily"
  

4. ORDER BY

• 可以加入 ASC or DESC 參數
• 結果會按照指定欄位依序排列，詳細說明請看 DocumentDB Order By 逐步解說

參考資料

1. DocumentDB 中的 SQL 查詢和 SQL 語法
2. Azure DocumentDB Query Playground
3. JSON 規格
4. 在 DocumentDB 中模型化資料
5. DocumentDB SQL Syntax
6. DocumentDB Order By 逐步解說